/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sessionBean;

import DBConnection.ATeamObject;
import goodsBean.*;
import categoryBean.*;
import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import javax.rmi.PortableRemoteObject;
import javax.sql.DataSource;
import javax.xml.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.validation.*;
import manufacturerBean.*;
import org.w3c.dom.*;
import org.xml.sax.*;


/**
 * Session Bean for working with Entity Beans
 * @author Konstantin
 */
public class SessionBean implements javax.ejb.SessionBean{
     
    private SessionContext sessionContext;
    private DataSource dataSource;
    private Connection connection;
    
    private HashMap<Integer, Collection> checkData(Collection allKeys)
            throws SQLException {
        
        String getAllGoodOfCategoryQuery =
                "SELECT ObjectId" +
                " FROM Object" +
                " WHERE OBJECTTYPEID = 4" +
                " AND ParentId = ?" +
                " ORDER BY ObjectId";
        
        String getIdCategoryOfGoodQuery =
                "SELECT OBJECT.PARENTID FROM Object" +
                " WHERE Object.ObjectId = ?" +
                " AND Object.ObjectTYPEID = 4";
        
        String getExportReferenceQuery = 
                "SELECT VALUE"
                + " FROM EXPREFERENCES"
                + " WHERE KEY = ?";
        
        String getObjectTypeIdQuery = 
                "SELECT ObjectTypeId"
                + " FROM Object"
                + " WHERE ObjectId = ?";
        
        String getObjectIdQuery =
                "SELECT OBJECTID"
                + " FROM Object"
                + " WHERE ObjectTYPEID = ?";
        
        String getReferenceIdQuery = 
                "SELECT ReferenceId"
                + " FROM Reference"
                + " WHERE ObjectId = ?"
                + " AND AttrId = 9";
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        HashMap<Integer, Collection> keys =
                new HashMap<Integer, Collection>();
        Collection categoriesID = new ArrayList<Integer>();
        Collection manufacturersID = new ArrayList<Integer>();
        Collection goodsID = new ArrayList<Integer>();
        Collection shopsID = new ArrayList<Integer>();
        Iterator allKeysIterator = null;
        Iterator categoriesIterator = null;
        Iterator goodsIterator = null;
        Integer objectId = null;
        
        try {
            
            allKeysIterator = allKeys.iterator();
            
            preparedStatement = connection.prepareStatement(
                        getObjectTypeIdQuery);
            
            while (allKeysIterator.hasNext()) {                
                
                objectId = (Integer)allKeysIterator.next();
                
                preparedStatement.setInt(1, objectId);
                resultSet = preparedStatement.executeQuery();
                
                if (resultSet.next()) {
                    
                    switch (resultSet.getInt(1)){

                        case 1 : {
                            categoriesID.add(objectId); 
                        } break;

                        case 2 : {
                            manufacturersID.add(objectId); 
                        } break;

                        case 4 : {
                            goodsID.add(objectId); 
                        } break;

                        case 5 : {
                            shopsID.add(objectId); 
                        } break;
                    }
                }    
            }
            
            if (shopsID.isEmpty()) {
                
                preparedStatement = connection.prepareStatement(
                            getObjectIdQuery);
                preparedStatement.setInt(1, 5);
                resultSet = preparedStatement.executeQuery();
                resultSet.next();
                shopsID.add(resultSet.getInt(1));
            }
            
            if (!categoriesID.isEmpty()) {
                
                categoriesIterator = categoriesID.iterator();
                
                while(categoriesIterator.hasNext()) {
                    
                    preparedStatement = connection.prepareStatement(
                            getAllGoodOfCategoryQuery);
                    preparedStatement.setInt(1,
                            (Integer)categoriesIterator.next());
                    resultSet = preparedStatement.executeQuery();
                    
                    if (resultSet.next()){
                        
                        do {
                            
                            if (!goodsID.contains(resultSet.getInt(1))){
                                
                                goodsID.add(resultSet.getInt(1));
                            }
                        } while (resultSet.next());
                    }    
                }
            }
            
            if (!goodsID.isEmpty()) {
                
                goodsIterator = goodsID.iterator();
                
                while(goodsIterator.hasNext()) {
                    preparedStatement = connection.prepareStatement(
                            getIdCategoryOfGoodQuery);
                    preparedStatement.setInt(1,
                            (Integer)goodsIterator.next());
                    resultSet = preparedStatement.executeQuery();
                    
                    if (resultSet.next()){
                        do {
                            if (!categoriesID.contains(resultSet.getInt(1))){
                                categoriesID.add(resultSet.getInt(1));
                            }
                        } while (resultSet.next());
                    }    
                }
            }
            
            preparedStatement = connection.prepareStatement(
                        getExportReferenceQuery);
            preparedStatement.setString(1, "searchByReference");
            resultSet = preparedStatement.executeQuery();
            
            if (!resultSet.next()){
                
                preparedStatement.close();
                throw new NoSuchEntityException("Export reference isn't found");
            }
            
            if ((!goodsID.isEmpty()) && resultSet.getString(1).equals("true")) {
                
                goodsIterator = goodsID.iterator();
                while (goodsIterator.hasNext()) {
                    preparedStatement = connection.prepareStatement(
                        getReferenceIdQuery);
                    preparedStatement.setInt(1, (Integer)goodsIterator.next());
                    resultSet = preparedStatement.executeQuery();
                    
                    if (resultSet.next()) {
                        if (!manufacturersID.contains(resultSet.getInt(1))) {
                            manufacturersID.add(resultSet.getInt(1));
                        }
                    }
                }
            }
            
            keys.put(1, categoriesID);
            keys.put(2, manufacturersID);
            keys.put(4, goodsID);
            keys.put(5, shopsID);
            
            return keys;
        } catch (SQLException ex) {
            
            throw new EJBException(ex); 
        }
    }
    
    private void closeConnection() {
        try {
            
            if (connection != null){
                connection.close();
                System.out.println("Connection closed");
            }
        } catch (SQLException ex) {
            
            throw new EJBException(ex);
        } finally {
            
            connection = null;
        }
    }
    
    public void ejbActivate() {
        
    }
    
    /**
     * Create new session
     * @return Remote Interface
     * @throws CreateException
     * @see CreateException
     */
    public void ejbCreate() throws CreateException {
    
    }
    
    public void ejbPassivate() {
        
    }
    
    public void ejbRemove() {
       
    }
    
    /**
     * Returns value of <i>searchByReference</i> from database
     * @return Boolean value of searchByReference
     * @see RemoteException
     */
    public Boolean getSearchByReference() {
        
        String getSearch =
                "SELECT Value"
                + " FROM EXPREFERENCES"
                + " WHERE Key = 'searchByReference'";
        
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        try {
            
            setConnection();
            
            ps = connection.prepareStatement(
                    getSearch);
            rs = ps.executeQuery();
            
            if (!rs.next()) {
                
                ps.close();
                throw new EJBException("Export reference isn't found");
            }
            
            if ((rs.getString("Value").toLowerCase()).equals("true")) {
                
                return true;
            } else {
                
                return false;
            }
        
        } catch (SQLException ex) {
            ex.printStackTrace(System.err);
            throw new EJBException (ex);
        
        } finally {
            
            closeConnection();
        }
    }
    
    public String getText(Node node){
        
        NodeList childNodes = node.getChildNodes();
        String newText = childNodes.item(0).getNodeValue().trim();
        return newText;
    }
    
    /**
     * Export objects from database into XML
     * @param exportKeys
     */
    public DOMSource getXML(HashMap<Integer, Collection> exportKeys) {
        
        Integer shopID;
        Collection categoriesID, manufacturersID, goodsID;
        
        DocumentBuilderFactory dbfac;
        DocumentBuilder docBuilder;
        Document document;
        
        Object homeCategoryObject;
        CategoryHome categoryHome;
        Category category;
        
        Object homeGoodsObject;
        GoodsHome goodsHome;
        Goods goods;

        Object homeManufacturerObject;
        ManufacturerHome manufacturerHome;
        Manufacturer manufacturer;

        PreparedStatement preparedStatement;
        ResultSet resultSet;
        
        String getDateQuery =
                "SELECT TO_DATE(VALUE, 'DD.MM.YYYY HH24:MI:SS') AS VALUE,"
                + " ATTRID FROM PARAMS"
                + " WHERE OBJECTID = ?"
                + " AND ATTRID IN (23,24)"
                + " ORDER BY ATTRID";
    
        String getNameQuery = 
                "SELECT Object.Name"
                + " FROM Object"
                + " WHERE ObjectId = ?"
                + " AND ObjectTypeId = ?";
        
        String getObjectDescriptionQuery = 
                "SELECT ObjectDescription"
                + " FROM Object"
                + " WHERE ObjectId = ?"
                + " AND ObjectTypeId = ?";
        
        setConnection();
        
        try {
            if (exportKeys.containsKey(0)){
                
                HashMap<Integer, Collection> keys = checkData(
                        exportKeys.get(0));
            
                if (keys.containsKey(5) && !keys.get(5).isEmpty()){
                    
                    shopID = (Integer)keys.get(5).iterator().next();

                    dbfac = DocumentBuilderFactory.newInstance();
                    dbfac.setNamespaceAware(true);
                    docBuilder = dbfac.newDocumentBuilder();
                    document = docBuilder.newDocument();

                    Element shop = document.createElement("shop");
                    shop.setAttribute("xmlns:xsi",
                            "http://www.w3.org/2001/XMLSchema-instance");
                    shop.setAttribute("xsi:noNamespaceSchemaLocation",
                            "http://localhost:8080/ATeamShop-war/shop.xsd");
                    shop.setAttribute("id", "_" + shopID.toString());
                    shop.setAttribute("objectTypeId", "5");
                    document.appendChild(shop);

                    preparedStatement = connection.prepareStatement(
                            getNameQuery);
                    preparedStatement.setInt(1, shopID);
                    preparedStatement.setInt(2, 5);
                    resultSet = preparedStatement.executeQuery(); 
                    if (!resultSet.next()){
                        throw new NoSuchEntityException(
                                "The shop name isn't found");
                    }

                    Element shopName = document.createElement("name");
                    shop.appendChild(shopName);
                    Text text = document.createTextNode(resultSet.getString(1));
                    resultSet = null;
                    shopName.appendChild(text);

                    preparedStatement = connection.prepareStatement(
                            getDateQuery);
                    preparedStatement.setInt(1, shopID);
                    resultSet = preparedStatement.executeQuery(); 
                    if (!resultSet.next()){
                        throw new NoSuchEntityException(
                                "The shop dates isn't found");
                    }
                    String createDate = resultSet.getString("Value");
                    resultSet.next();
                    String lastModifyDate = resultSet.getString("Value");
                    resultSet = null;

                    Element shopCreateDate = document.createElement(
                            "createDate");
                    shop.appendChild(shopCreateDate);
                    text = document.createTextNode(createDate.toString());
                    shopCreateDate.appendChild(text);

                    Element shopLastModifyDate = document.createElement(
                            "lastModifyDate");
                    shop.appendChild(shopLastModifyDate);
                    text = document.createTextNode(lastModifyDate.toString());
                    shopLastModifyDate.appendChild(text);

                    preparedStatement = connection.prepareStatement(
                            getObjectDescriptionQuery);
                    preparedStatement.setInt(1, shopID);
                    preparedStatement.setInt(2, 5);
                    resultSet = preparedStatement.executeQuery(); 
                    if (!resultSet.next()){
                        throw new NoSuchEntityException(
                                "The shop description isn't found");
                    }

                    Element shopDescription = document.createElement(
                                    "description");
                    shop.appendChild(shopDescription);
                    text = document.createTextNode(resultSet.getString(1));
                    shopDescription.appendChild(text);

                    if (keys.containsKey(1) && !keys.get(1).isEmpty()){
                        
                        categoriesID = keys.get(1);

                        homeCategoryObject = new InitialContext().lookup(
                                "CategoryBean");
                        categoryHome = (CategoryHome)PortableRemoteObject.
                                narrow(homeCategoryObject,CategoryHome.class);
                        Iterator categoriesIterator = categoriesID.iterator();

                        while (categoriesIterator.hasNext()){
                            category = categoryHome.findByPrimaryKey(
                                    (Integer)categoriesIterator.next());

                            Element shopCategory = document.createElement(
                                    "object");
                            shopCategory.setAttribute(
                                    "id", "_" + category.getObjectId().
                                    toString());
                            shopCategory.setAttribute("objectTypeId", "1");
                            shopCategory.setAttribute(
                                    "parentId", category.getParentId().
                                    toString());
                            shop.appendChild(shopCategory);

                            Element categoryName = document.createElement(
                                    "name");
                            shopCategory.appendChild(categoryName);
                            text = document.createTextNode(category.getName());
                            categoryName.appendChild(text);

                            Element categoryCreateDate = document.createElement(
                                    "createDate");
                            shopCategory.appendChild(categoryCreateDate);
                            text = document.createTextNode(
                                    category.getCreateDate().toString());
                            categoryCreateDate.appendChild(text);

                            Element categoryLastModifyDate = document.
                                    createElement("lastModifyDate");
                            shopCategory.appendChild(categoryLastModifyDate);
                            text = document.createTextNode(
                                    category.getLastModifyDate());
                            categoryLastModifyDate.appendChild(text);

                            Element categoryDescription = document.
                                    createElement("description");
                            shopCategory.appendChild(categoryDescription);
                            text = document.createTextNode(
                                    category.getObjectDescription());
                            categoryDescription.appendChild(text);
                        }    
                    }

                    if (keys.containsKey(2) && !keys.get(2).isEmpty()){
                        
                        manufacturersID = keys.get(2);

                        homeManufacturerObject = new InitialContext().lookup(
                                "ManufacturerBean");
                        manufacturerHome = (ManufacturerHome)
                                PortableRemoteObject.narrow(
                                homeManufacturerObject,ManufacturerHome.class);
                        Iterator manufacturersIterator = manufacturersID.
                                iterator();

                        while (manufacturersIterator.hasNext()){
                            manufacturer = manufacturerHome.findByPrimaryKey(
                                    (Integer)manufacturersIterator.next());

                            Element shopManufacturer = document.createElement(
                                    "object");
                            shopManufacturer.setAttribute("id", "_"
                                    + manufacturer.getObjectId().toString());
                            shopManufacturer.setAttribute("objectTypeId", "2");
                            shopManufacturer.setAttribute("parentId",
                                    manufacturer.getParentId().toString());
                            shop.appendChild(shopManufacturer);

                            Element manufacturerName = document.createElement(
                                    "name");
                            shopManufacturer.appendChild(manufacturerName);
                            text = document.createTextNode(manufacturer.
                                    getName());
                            manufacturerName.appendChild(text);

                            Element manufacturerCreateDate = document.
                                    createElement("createDate");
                            shopManufacturer.appendChild(
                                    manufacturerCreateDate);
                            text = document.createTextNode(
                                    manufacturer.getCreateDate());
                            manufacturerCreateDate.appendChild(text);

                            Element manufacturerLastModifyDate =
                                    document.createElement("lastModifyDate");
                            shopManufacturer.appendChild(
                                    manufacturerLastModifyDate);
                            text = document.createTextNode(
                                    manufacturer.getLastModifyDate());
                            manufacturerLastModifyDate.appendChild(text);

                            Element manufacturerDescription =
                                    document.createElement("description");
                            shopManufacturer.appendChild(
                                    manufacturerDescription);
                            text = document.createTextNode(
                                    manufacturer.getObjectDescription().
                                    toString());
                            manufacturerDescription.appendChild(text);
                        }    
                    }    

                    if (keys.containsKey(4) && !keys.get(4).isEmpty()){
                        
                        goodsID = keys.get(4);

                        homeGoodsObject = new InitialContext().lookup(
                                "GoodsBean");
                        goodsHome = (GoodsHome)PortableRemoteObject.narrow(
                                homeGoodsObject,GoodsHome.class);

                        Iterator goodsIterator = goodsID.iterator();

                        while (goodsIterator.hasNext()){
                            goods = goodsHome.findByPrimaryKey(
                                    (Integer)goodsIterator.next());

                            Element shopGoods = document.createElement(
                                    "object");
                            shopGoods.setAttribute(
                                    "id", "_" + String.valueOf(goods.
                                    getGoodsId()));
                            shopGoods.setAttribute("objectTypeId", "4");
                            shopGoods.setAttribute("parentId",
                                    String.valueOf(goods.getCategoryId()));
                            shop.appendChild(shopGoods);

                            Element goodsName = document.createElement("name");
                            shopGoods.appendChild(goodsName);
                            text = document.createTextNode(goods.
                                    getNameOfGoods());
                            goodsName.appendChild(text);

                            Element goodsCreateDate = document.createElement(
                                    "createDate");
                            shopGoods.appendChild(goodsCreateDate);
                            text = document.createTextNode(
                                    goods.getGoodsCreateDate().toString());
                            goodsCreateDate.appendChild(text);

                            Element goodsLastModifyDate = document.
                                    createElement("lastModifyDate");
                            shopGoods.appendChild(goodsLastModifyDate);
                            text = document.createTextNode(
                                    goods.getGoodsLastModifyDate().toString());
                            goodsLastModifyDate.appendChild(text);

                            Element goodsDescription = document.createElement(
                                    "description");
                            shopGoods.appendChild(goodsDescription);
                            text = document.createTextNode(
                                    goods.getGoodsDescription().toString());
                            goodsDescription.appendChild(text);

                            Element goodsManufacturer = document.createElement(
                                    "manufacturerId");
                            shopGoods.appendChild(goodsManufacturer);
                            text = document.createTextNode(
                                    Integer.toString(goods.getManufactureId()));
                            goodsManufacturer.appendChild(text);

                            Element goodsPrice = document.createElement(
                                    "price");
                            shopGoods.appendChild(goodsPrice);
                            text = document.createTextNode(
                                    Integer.toString(goods.getPrice()));
                            goodsPrice.appendChild(text);

                            Element goodsNumber = document.createElement(
                                    "number");
                            shopGoods.appendChild(goodsNumber);
                            text = document.createTextNode(
                                    Integer.toString(goods.getNumber()));
                            goodsNumber.appendChild(text);
                        }     
                    }
                    
                    validate(docBuilder,document);
                    return new DOMSource(document);
                } else {
                    
                    throw new EJBException("Root element is missed");
                }
            } else {
                
                throw new EJBException("Input data is missed");
            }    
            
        } catch (Exception ex) {
            
            
            throw new EJBException(ex);
        } finally {
            closeConnection();
        }
    }
    
    /**
     * Import objects from XML into database
     * @param path Address of XML file 
     * @return Collection with solvable and unsolvable conflicts
     */
    public Collection[] importFile(String path) {
        
        Collection[] coll = new ArrayList[2];
        coll[0] = new ArrayList();
        coll[1] = new ArrayList();
        
        try {
            
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            dbf.setNamespaceAware(true);
            DocumentBuilder db = null;
            try {
                db = dbf.newDocumentBuilder();
            }
            catch(ParserConfigurationException pce) {
                
                throw new EJBException(pce);
            }
            
            Document document = null;
            document = db.parse(path);
            
            SchemaFactory schemaFactory = SchemaFactory.newInstance(
            XMLConstants.W3C_XML_SCHEMA_NS_URI);
            Schema schemaXSD = schemaFactory.newSchema();
            Validator validator = schemaXSD.newValidator();
            
            DOMSource domSource = new DOMSource(document);
            
            try {
                
                validator.validate(domSource);
            } catch (SAXException ex) {
                
                throw new SAXException("Validation it is not passed:"
                        + ex.getMessage());
            }    
            
            Node node = document.getDocumentElement();
            
            NodeList childNodes = node.getChildNodes();
                if(childNodes!=null){
                    int length = childNodes.getLength();
                    for(int loopIndex=0; loopIndex<length; loopIndex++){
                        node = childNodes.item(loopIndex);
                        String name = node.getNodeName();
                        if("object".equals(name)){
                            ATeamObject ato = new ATeamObject();
                            
                            int lengthAttr = (node.getAttributes()!=null) ? 
                            node.getAttributes().getLength() : 0;
                            Attr attributes[] = new Attr[lengthAttr];
                            for(int attrIndex=0; attrIndex<lengthAttr;
                                    attrIndex++){
                                attributes[attrIndex] = 
                                (Attr)node.getAttributes().item(attrIndex);
                            }
                    
                            for(int attrInd=0; attrInd<attributes.length;
                                    attrInd++){
                            Attr attribute = attributes[attrInd];
                        
                                if("id".equals(attribute.getNodeName())){
                                    ato.setObjectId(
                                            new Integer((
                                       attribute.getNodeValue()).substring(1)).
                                            intValue());
                                }
                        
                                if("parentId".equals(attribute.getNodeName())){
                                    ato.setParentId(
                                    new Integer(attribute.getNodeValue()).
                                            intValue());
                                }
                        
                                if("objectTypeId".equals(
                                        attribute.getNodeName())){
                                    ato.setTypeOfObject(
                                    new Integer(attribute.getNodeValue()).
                                            intValue());
                                }
                            }
                            
                            NodeList childObject = node.getChildNodes();
                            if(childObject!=null){
                                int lengthChild = childObject.getLength();
                                for(int childIndex=0; childIndex<lengthChild;
                                        childIndex++){
                                    node = childObject.item(childIndex);
                                    name = node.getNodeName();
                                    if("name".equals(name)){
                                        ato.setName(getText(node));
                                    }
                                    if("createDate".equals(name)){
                                        ato.setCreateDate(getText(node));
                                    }
                                    if("lastModifyDate".equals(name)){
                                        ato.setLastModifyDate(getText(node));
                                    }
                                    if("price".equals(name)){
                                        ato.setPrice(new Integer(
                                                getText(node)).intValue());
                                    }
                                    if("number".equals(name)){
                                        ato.setNumber(new Integer(
                                                getText(node)).intValue());
                                    }
                                    if("description".equals(name)){
                                        ato.setObjectDescription(getText(node));
                                    }
                                    if("manufacturerId".equals(name)){
                                        ato.setManifacturerId(new Integer(
                                            getText(node)).intValue());
                                    }
                                }
                            }
                            
                            setConnection();
                            try{ 
                                String search="SELECT ObjectTypeId"
                                        + " FROM Object"+
                                    " WHERE ObjectId =?";
                                PreparedStatement preparedStatement = 
                                    connection.prepareStatement(search);
                                preparedStatement.setInt(1, ato.getObjectId());
                                ResultSet result = 
                                        preparedStatement.executeQuery();
                                
                                String searchParent="SELECT ObjectId "
                                        + "FROM Object"+
                                    " WHERE ObjectId =?"+
                                " AND ObjectTypeId IN (SELECT ParentId"
                                        + " FROM ObjectType"
                                        + " WHERE ObjectTypeId = ? )";
                                PreparedStatement searchParentStat = 
                                    connection.prepareStatement(searchParent);
                                searchParentStat.setInt(1, ato.getParentId());
                                searchParentStat.setInt(2, 
                                        ato.getTypeOfObject());
    
                                ResultSet resParent = 
                                        searchParentStat.executeQuery();
                                
                                if(result.next()){
                                    if(result.getInt("ObjectTypeId")==
                                            ato.getTypeOfObject()
                                            &&(resParent.next())){
                                        coll[0].add(ato);
                                    }
                                    else{
                                        coll[1].add(ato);
                                    }
                                }
                                else {
                                    if(resParent.next()) {
                                        InitialContext initialContext =
                                                new InitialContext();
                                        switch(ato.getTypeOfObject()){
                                            case 1:{
                                                Object homeObject = 
                                                initialContext.lookup(
                                                        "CategoryBean");
                                                CategoryHome catHome = 
                                                (CategoryHome) 
                                                PortableRemoteObject.narrow(
                                                homeObject, CategoryHome.class);
                                                catHome.create(ato);
                                                break;
                                            }
                                            
                                            case 2:{
                                                Object homeObject = 
                                                        initialContext.lookup(
                                                        "ManufacturerBean");
                                                ManufacturerHome manHome = 
                                                (ManufacturerHome) 
                                                PortableRemoteObject.narrow(
                                                homeObject,
                                                ManufacturerHome.class);
                                                manHome.create(ato);
                                                break;
                                            }
                                            
                                            case 4: {
                                                Object homeObject = 
                                                        initialContext.lookup(
                                                        "GoodsBean");
                                                GoodsHome gooHome = (GoodsHome)
                                                        PortableRemoteObject.
                                                        narrow(homeObject,
                                                        GoodsHome.class);
                                                gooHome.create(ato);
                                                break;
                                            }
                                        }
                                    } else {
                                        coll[1].add(ato);
                                    } 
                                }
                            }
                            catch(Exception ex) {
                                
                                ex.printStackTrace(System.err);
                                throw new EJBException(ex);
                            }
                        }    
                    }
                }
        }
        catch (Exception ex) {
            
            ex.printStackTrace(System.err);
            throw new EJBException(ex);
        } finally {
            
            closeConnection();
        }
        return coll;
    }
    
    private void setConnection(){ 
        
        InitialContext initialContext = null;
        
        try {
            
            initialContext = new InitialContext();
            dataSource = (DataSource) initialContext.lookup(
                        "java:/ATeamConnectionPool");
            System.out.println("dataSource is found");                
        } catch (NamingException ex) {
            
            throw new EJBException(ex);
        }
        if (connection == null){
            try {
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            } catch (SQLException ex){
                
                throw new EJBException(ex);
            }
        }    
    }
    
    /**
     * Sets searchByReference in database
     * @param useReference
     */
    public void setSearchByReference (Boolean useReference) {
        String setSearch =
                "UPDATE EXPREFERENCES"
                + " SET Value = ?"
                + " WHERE Key = 'searchByReference'";
        
        setConnection();
        
        try {
            PreparedStatement preparedStatement =
                    connection.prepareStatement(setSearch);
            preparedStatement.setString(1, useReference.toString());
            
            if (preparedStatement.executeUpdate() != 1) {
                throw new NoSuchEntityException(
                        "Unable to set search by reference");
            }
        } catch (SQLException ex) {
            throw new EJBException (ex);
        } finally {
            closeConnection();
        }        
    }
            
    public void setSessionContext(SessionContext ctx)
            throws EJBException, RemoteException {
        sessionContext = ctx;
    }
    
    private void validate(DocumentBuilder docBuilder, Document document) {
        
        DOMSource domSource = null;
        
        try {
            
            domSource = new DOMSource(document);
            
            TransformerFactory transformerFactory =
                    TransformerFactory.newInstance();
            Transformer transformer = transformerFactory.newTransformer();
            transformer.setOutputProperty(
                    OutputKeys.OMIT_XML_DECLARATION, "no");
            transformer.setOutputProperty(OutputKeys.INDENT, "yes");
            transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
            transformer.setOutputProperty(OutputKeys.VERSION, "1.0");
            
            SchemaFactory schemaFactory = SchemaFactory.newInstance(
            XMLConstants.W3C_XML_SCHEMA_NS_URI);
            ErrorHandler lenient = new ForgivingErrorHandler();
            Schema schemaXSD = schemaFactory.newSchema();
            Validator validator = schemaXSD.newValidator();
            validator.setErrorHandler(lenient);
            
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            StreamResult streamResult = new StreamResult(baos);
            transformer.transform(domSource, streamResult);
 
            ByteArrayInputStream bais = new ByteArrayInputStream(
                    baos.toByteArray());
            document = docBuilder.parse(bais);
            domSource = new DOMSource(document);
            validator.validate(domSource);
        } catch (Exception ex) {
            
            ex.printStackTrace(System.err);
            throw new EJBException(ex);
        }
    }
    
    class ForgivingErrorHandler implements ErrorHandler {

        public void warning(SAXParseException ex) {
            System.err.println(ex.getMessage());
        }

        public void error(SAXParseException ex) {
            System.err.println(ex.getMessage());
        }

        public void fatalError(SAXParseException ex) throws SAXException {
            throw ex;
        }
    }
}
